﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using AlunoLib.Basic;

namespace AlunoLib.Repositorio
{
    public class RepAluno
    {
        private SqlConnection conexao;
        private string stringConexao = @"Data Source=VITOR-PC\SQLEXPRESS;Initial Catalog=Escola; user id=sa; pwd=jva;";
        private static RepAluno instancia;

        private RepAluno()
        {
        }


        public static RepAluno getInstancia()
        {
            if (instancia == null)
            {
                instancia = new RepAluno();
                return instancia;
            }
            return instancia;
        }


        public void inserir(Aluno obj)
        {

            conexao = new SqlConnection(stringConexao);

            try
            {

                SqlCommand sql = new SqlCommand("insert into Aluno values (@NomeAluno, @Endereço, @Telefone, @Email, @Sexo)", conexao);

                sql.Parameters.AddWithValue("@NomeAluno", obj.Nome);
                sql.Parameters.AddWithValue("@Endereço", obj.Endereco);
                sql.Parameters.AddWithValue("@Telefone", obj.Telefone);
                sql.Parameters.AddWithValue("@Email", obj.Email);
                sql.Parameters.AddWithValue("@Sexo", obj.Sexo);

                conexao.Open();

                sql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }

        }

        public void alterar(Aluno obj)
        {
            conexao = new SqlConnection(stringConexao);

            try
            {

                SqlCommand sql = new SqlCommand("update Aluno set NomeAluno=@NomeAluno, Endereço=@endereço, Telefone=@Telefone, Email=@Email, Sexo=@Sexo where MatAluno=@MatAluno", conexao);

                sql.Parameters.AddWithValue("@MatAluno", obj.Matricula);
                sql.Parameters.AddWithValue("@NomeAluno", obj.Nome);
                sql.Parameters.AddWithValue("@Endereço", obj.Endereco);
                sql.Parameters.AddWithValue("@Telefone", obj.Telefone);
                sql.Parameters.AddWithValue("@Email", obj.Email);
                sql.Parameters.AddWithValue("@Sexo", obj.Sexo);

                conexao.Open();

                sql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public List<Aluno> listar()
        {

            conexao = new SqlConnection(stringConexao);

            try
            {
                List<Aluno> lista = new List<Aluno>();
                SqlCommand sql = new SqlCommand("select * from Aluno order by NomeAluno", conexao);

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Aluno aluno = new Aluno();
                    aluno.Matricula = Convert.ToInt32(dr["MatAluno"]);
                    aluno.Nome = dr["NomeAluno"].ToString();
                    aluno.Endereco = dr["Endereço"].ToString();
                    aluno.Email = dr["Email"].ToString();
                    aluno.Telefone = dr["Telefone"].ToString();
                    aluno.Sexo = dr["Sexo"].ToString();
                    lista.Add(aluno);
                }
                return lista;

            }
            catch (Exception e)
            {

                throw e;
            }


            finally
            {

                conexao.Dispose();

            }
        }

        public List<Aluno> Pesquisar(string nome,string matricula)
        {

            conexao = new SqlConnection(stringConexao);

            try
            {
                List<Aluno> lista = new List<Aluno>();
                SqlCommand sql = new SqlCommand("select * from Aluno where NomeAluno like @NomeAluno and MatAluno like @Matricula order by MatAluno", conexao);
                sql.Parameters.AddWithValue("@NomeAluno", nome + '%');
                sql.Parameters.AddWithValue("@Matricula", matricula + '%');

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Aluno aluno = new Aluno();
                    aluno.Matricula = Convert.ToInt32(dr["MatAluno"]);
                    aluno.Nome = dr["NomeAluno"].ToString();
                    aluno.Endereco = dr["Endereço"].ToString();
                    aluno.Email = dr["Email"].ToString();
                    aluno.Telefone = dr["Telefone"].ToString();
                    aluno.Sexo = dr["Sexo"].ToString();
                    lista.Add(aluno);
                }
                return lista;
            }
            catch (Exception e)
            {

                throw e;
            }


            finally
            {

                conexao.Dispose();

            }
        }

        public List<Responsavel> responsavel(int matricula)
        {
            conexao = new SqlConnection(stringConexao);

            try
            {
                List<Responsavel> lista = new List<Responsavel>();
                SqlCommand sql = new SqlCommand("select r.CodResp, r.NomeResp, r.Endereço, r.Telefone, r.Email, r.Sexo, r.Filiação, r.Categoria, a.MatAluno from Responsavel r inner join Aluno_Responsavel on r.CodResp=Aluno_Responsavel.CodResp inner join Aluno a on a.MatAluno=Aluno_Responsavel.MatAluno where Aluno_Responsavel.MatAluno=@MatAluno", conexao);

                sql.Parameters.AddWithValue("@MatAluno", matricula);

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Responsavel responsavel = new Responsavel();
                    responsavel.CodResp1 = Convert.ToInt32(dr["CodResp"]);
                    responsavel.Nome = dr["NomeResp"].ToString();
                    responsavel.Endereco = dr["Endereço"].ToString();
                    responsavel.Telefone = dr["Telefone"].ToString();
                    responsavel.Email = dr["Email"].ToString();
                    responsavel.Sexo = dr["Sexo"].ToString();
                    responsavel.Filiacao = dr["Filiação"].ToString();
                    responsavel.Categoria = dr["Categoria"].ToString();
                    lista.Add(responsavel);
                }
                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }
    }
}
